﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;


namespace DAO
{
    public class Nhacungcap_DAO
    {
        public List<Nhacungcap_DTO> LayDanhSachNCC()
        {
            string sql = "SELECT * FROM NHACUNGCAP";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<Nhacungcap_DTO> danhsach = new List<Nhacungcap_DTO>();
            while (reader.Read())
            {
                Nhacungcap_DTO NccDTO = new Nhacungcap_DTO();
                NccDTO.MaNCC = reader.GetInt32(0);
                NccDTO.TenNCC = reader.GetString(1);
                NccDTO.SDT = reader.GetInt32(2);
                NccDTO.Email = reader.GetString(3);
                NccDTO.Diachi = reader.GetString(4);
                NccDTO.Fax = reader.GetString(5);
                NccDTO.Website = reader.GetString(6);
                NccDTO.Taikhoan = reader.GetString(7);
                NccDTO.TinhTrang = reader.GetString(8);
                NccDTO.GhiChu = reader.GetString(9);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }

        public List<Nhacungcap_DTO> LayDanhSachNCC_Ten(string hoten)
        {
            string sql = "SELECT * FROM NHACUNGCAP WHERE TenNCC LIKE '%" + hoten + "%'";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<Nhacungcap_DTO> danhsach = new List<Nhacungcap_DTO>();
            while (reader.Read())
            {
                Nhacungcap_DTO NccDTO = new Nhacungcap_DTO();
                NccDTO.MaNCC = reader.GetInt32(0);
                NccDTO.TenNCC = reader.GetString(1);
                NccDTO.SDT = reader.GetInt32(2);
                NccDTO.Email = reader.GetString(3);
                NccDTO.Diachi = reader.GetString(4);
                NccDTO.Fax = reader.GetString(5);
                NccDTO.Website = reader.GetString(6);
                NccDTO.Taikhoan = reader.GetString(7);
                NccDTO.TinhTrang = reader.GetString(8);
                NccDTO.GhiChu = reader.GetString(9);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int ThemNhaCungCap(Nhacungcap_DTO cnDTO)
        {
            string sql = "INSERT INTO NHACUNGCAP(TenNCC,DienThoai,Email,DiaChi,Fax,WebSite,TaiKhoanNH,TinhTrang,GhiChu) VALUES(@TenNCC,@DienThoai,@Email,@DiaChi,@Fax,@WebSite,@TaiKhoanNH,@TinhTrang,@GhiChu)";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@TenNCC", OleDbType.VarChar);
            para.Value = cnDTO.TenNCC;
            para = cmd.Parameters.Add("@DienThoai", OleDbType.VarChar);
            para.Value = cnDTO.SDT;
            para = cmd.Parameters.Add("@Email", OleDbType.VarChar);
            para.Value = cnDTO.Email;
            para = cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            para.Value = cnDTO.Diachi;
            para = cmd.Parameters.Add("@Fax", OleDbType.VarChar);
            para.Value = cnDTO.Fax;
            para = cmd.Parameters.Add("@WebSite", OleDbType.VarChar);
            para.Value = cnDTO.Fax;
            para = cmd.Parameters.Add("@TaiKhoanNH", OleDbType.VarChar);
            para.Value = cnDTO.Taikhoan;
            para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);
            para.Value = cnDTO.TinhTrang;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int CapNhatNhaCungCap(Nhacungcap_DTO cnDTO)
        {
            string sql = "UPDATE NHACUNGCAP SET TenNCC=@TenNCC,DienThoai=@DienThoai,Email=@Email,DiaChi=@DiaChi,Fax=@Fax,WebSite=@WebSite,TaiKhoanNH=@TaiKhoanNH,TinhTrang=@TinhTrang,GhiChu=@GhiChu WHERE MaNCC=@MaNCC";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@TenNCC", OleDbType.VarChar);
            para.Value = cnDTO.TenNCC;
            para = cmd.Parameters.Add("@DienThoai", OleDbType.VarChar);
            para.Value = cnDTO.SDT;
            para = cmd.Parameters.Add("@Email", OleDbType.VarChar);
            para.Value = cnDTO.Email;
            para = cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            para.Value = cnDTO.Diachi;
            para = cmd.Parameters.Add("@Fax", OleDbType.VarChar);
            para.Value = cnDTO.Fax;
            para = cmd.Parameters.Add("@WebSite", OleDbType.VarChar);
            para.Value = cnDTO.Website;
            para = cmd.Parameters.Add("@TaiKhoanNH", OleDbType.VarChar);
            para.Value = cnDTO.Taikhoan;
            para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);
            para.Value = cnDTO.TinhTrang;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;
            para = cmd.Parameters.Add("@MaNCC", OleDbType.VarChar);
            para.Value = cnDTO.MaNCC;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int XoaNhaCungCap(string mancc)
        {
            string sql = "DELETE FROM NHACUNGCAP WHERE MaNCC=" + mancc + "";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }

    }

    public class MatHang_DAO
    {
        public List<MatHang_DTO> LayDanhSachMatHang(string maHD, string loaimh)
        {
            string sql = "SELECT * FROM " + loaimh + " WHERE MaHD =" + maHD;

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<MatHang_DTO> danhsach = new List<MatHang_DTO>();
            while (reader.Read())
            {
                MatHang_DTO NccDTO = new MatHang_DTO();
                NccDTO.MaHang = reader.GetInt32(0);
                NccDTO.TenHang = reader.GetString(1);
                NccDTO.MaHD = reader.GetInt32(2);
                NccDTO.MaXuatXu = reader.GetInt32(3);
                NccDTO.IDLoai = reader.GetInt32(4);
                NccDTO.DVTinh = reader.GetString(5);
                NccDTO.SoLuong = reader.GetInt32(6);
                NccDTO.GiaNhap = reader.GetInt32(7);
                NccDTO.DonGia = reader.GetInt32(8);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int ChiTietMatHang(MatHang_DTO cnDTO, string loaimh)
        {
            string sql = "INSERT INTO " + loaimh + "(TenHang,MaHD,XuatXu,IDLoai,DVTinh,SoLuong,GiaNhap,DonGia) VALUES(@TenHang,@MaHD,@XuatXu,@IDLoai,@DVTinh,@SoLuong,@GiaNhap,@DonGia)";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
            para.Value = cnDTO.TenHang;
            para = cmd.Parameters.Add("@MaHD", OleDbType.VarChar);
            para.Value = cnDTO.MaHD;
            para = cmd.Parameters.Add("@XuatXu", OleDbType.VarChar);
            para.Value = cnDTO.MaXuatXu;
            para = cmd.Parameters.Add("@IDLoai", OleDbType.VarChar);
            para.Value = cnDTO.IDLoai;
            para = cmd.Parameters.Add("@DVTinh", OleDbType.VarChar);
            para.Value = cnDTO.DVTinh;
            para = cmd.Parameters.Add("@SoLuong", OleDbType.VarChar);
            para.Value = cnDTO.SoLuong;
            para = cmd.Parameters.Add("@GiaNhap", OleDbType.VarChar);
            para.Value = cnDTO.GiaNhap;
            para = cmd.Parameters.Add("@DonGia", OleDbType.VarChar);
            para.Value = cnDTO.DonGia;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int XoaMatHang(string MaHang, string loaimh)
        {
            string sql = "DELETE FROM " + loaimh + " WHERE MaHang= " + MaHang;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public List<MatHang_DTO> LayDanhSachMH()
        {
            string sql = "SELECT * FROM MATHANG";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<MatHang_DTO> danhsach = new List<MatHang_DTO>();
            while (reader.Read())
            {
                if (reader.GetInt32(4) > 0)
                {
                    MatHang_DTO qlmtDTO = new MatHang_DTO();
                    qlmtDTO.MaHang = reader.GetInt32(0);
                    qlmtDTO.TenHang = reader.GetString(1);
                    qlmtDTO.MaXuatXu = reader.GetInt32(2);
                    qlmtDTO.DVTinh = reader.GetString(3);
                    qlmtDTO.SoLuong = reader.GetInt32(4);
                    qlmtDTO.GiaNhap = reader.GetInt32(5);
                    qlmtDTO.TinhTrang = reader.GetString(6);
                    qlmtDTO.GhiChu = reader.GetString(7);
                    qlmtDTO.DonGia = reader.GetInt32(8);
                    qlmtDTO.IDLoai = reader.GetInt32(9);
                    danhsach.Add(qlmtDTO);
                }
            }
            conn.Close();
            return danhsach;
        }
        public int NhapHang(MatHang_DTO mh)
        {
            string sql = "SELECT * FROM MATHANG WHERE TenHang='" + mh.TenHang + "'";

            OleDbConnection conn;
            OleDbCommand cmd;
            conn = DataProvider.ConnectDB();
            cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                sql = "UPDATE MATHANG SET SoLuongTon=SoLuongTon+@SoLuongTon WHERE TenHang=@TenHang";
                cmd = new OleDbCommand(sql, conn);
                OleDbParameter para;
                para = cmd.Parameters.Add("@SoLuongTon", OleDbType.VarChar);
                para.Value = mh.SoLuong;
                para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
                para.Value = mh.TenHang;
                
            }
            else
            {
                sql = "INSERT INTO MATHANG (TenHang,XuatXu,DVTinh,SoLuongTon,TinhTrang,GhiChu,GiaNhap,DonGia,IDLoai) VALUES(@TenHang,@XuatXu,@DVTinh,@SoLuongTon,@TinhTrang,@GhiChu,@GiaNhap,@DonGia,@IDLoai)";
                cmd = new OleDbCommand(sql, conn);
                OleDbParameter para;
                para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
                para.Value = mh.TenHang;
                para = cmd.Parameters.Add("@XuatXu", OleDbType.VarChar);
                para.Value = mh.MaXuatXu;
                para = cmd.Parameters.Add("@DVTinh", OleDbType.VarChar);
                para.Value = mh.DVTinh;
                para = cmd.Parameters.Add("@SoLuongTon", OleDbType.VarChar);
                para.Value = mh.SoLuong;
                para = cmd.Parameters.Add("@TinhTrang", OleDbType.VarChar);
                para.Value = mh.TinhTrang;
                para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
                para.Value = mh.GhiChu;
                para = cmd.Parameters.Add("@GiaNhap", OleDbType.VarChar);
                para.Value = mh.GiaNhap;
                para = cmd.Parameters.Add("@DonGia", OleDbType.VarChar);
                para.Value = mh.DonGia;
                para = cmd.Parameters.Add("@IDloai", OleDbType.VarChar);
                para.Value = mh.IDLoai;
            }
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
       
        public int TraHang(MatHang_DTO mh)
        {
            string sql = "SELECT * FROM MATHANG WHERE TenHang='" + mh.TenHang + "'";

            OleDbConnection conn;
            OleDbCommand cmd;
            conn = DataProvider.ConnectDB();
            cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                if (mh.SoLuong > reader.GetInt32(4))
                {
                    return 3;
                }
                sql = "UPDATE MATHANG SET SoLuongTon=SoLuongTon-@SoLuongTon WHERE TenHang=@TenHang";
                cmd = new OleDbCommand(sql, conn);
                OleDbParameter para;
                para = cmd.Parameters.Add("@SoLuongTon", OleDbType.VarChar);
                para.Value = mh.SoLuong;
                para = cmd.Parameters.Add("@TenHang", OleDbType.VarChar);
                para.Value = mh.TenHang;

            }
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
    }

    public class HoaDonNhap_DAO
    {
        public List<HoaDonNhap_DTO> LayDanhSachHoaDon(int MaNCC)
        {
            string sql = "SELECT * FROM PHIEUNHAPHANG WHERE MaNCC=" + MaNCC;

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
           
            List<HoaDonNhap_DTO> danhsach = new List<HoaDonNhap_DTO>();
            while (reader.Read())
            {
                HoaDonNhap_DTO NccDTO = new HoaDonNhap_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayNhap = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
       
        public List<HoaDonNhap_DTO> LayDSTheoMaHD(string MaHD)
        {
            string sql = "SELECT * FROM PHIEUNHAPHANG WHERE MaHD=" + MaHD;

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<HoaDonNhap_DTO> danhsach = new List<HoaDonNhap_DTO>();
            while (reader.Read())
            {
                HoaDonNhap_DTO NccDTO = new HoaDonNhap_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayNhap = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
        public List<HoaDonNhap_DTO> LayMaHDMoi()
        {
            string sql = "SELECT TOP 1 * FROM PHIEUNHAPHANG ORDER BY MaHD DESC";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<HoaDonNhap_DTO> danhsach = new List<HoaDonNhap_DTO>();
            while (reader.Read())
            {
                HoaDonNhap_DTO NccDTO = new HoaDonNhap_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayNhap = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            return danhsach;
        }
        public int LapHoaDon(HoaDonNhap_DTO cnDTO)
        {
            string sql = "INSERT INTO PHIEUNHAPHANG(MaNCC,NgayLap,NgayNhap,TrangThai,GhiChu) VALUES(@MaNCC,@NgayLap,@NgayNhap,@TrangThai,@GhiChu)";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@MaNCC", OleDbType.VarChar);
            para.Value = cnDTO.MaNCC;
            para = cmd.Parameters.Add("@NgayLap", OleDbType.DBDate);
            para.Value = cnDTO.NgayLap;
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.DBDate);
            para.Value = cnDTO.NgayNhap;
            para = cmd.Parameters.Add("@TrangThai", OleDbType.VarChar);
            para.Value = cnDTO.TrangThai;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int CapNhatHoaDonNhap(HoaDonNhap_DTO cnDTO)
        {
            string sql = "UPDATE PHIEUNHAPHANG SET MaNCC=@MaNCC,NgayNhap=@NgayNhap,TrangThai=@TrangThai,GhiChu=@GhiChu WHERE MaHD=@MaHD";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@MaNCC", OleDbType.VarChar);
            para.Value = cnDTO.MaNCC;
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.DBDate);
            para.Value = cnDTO.NgayNhap;
            para = cmd.Parameters.Add("@TrangThai", OleDbType.VarChar);
            para.Value = cnDTO.TrangThai;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;
            para = cmd.Parameters.Add("@MaHD", OleDbType.VarChar);
            para.Value = cnDTO.MaHD;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int XoaHoaDonNhap(string MaHD)
        {
            string sql = "DELETE FROM PHIEUNHAPHANG WHERE MaHD=" + MaHD;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
    }
    public class HoaDonTra_DAO
    {
        public List<HoaDonTra_DTO> LayDanhSachHoaDon(int MaNCC)
        {
            string sql = "SELECT * FROM PHIEUTRAHANG WHERE MaNCC=" + MaNCC;

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<HoaDonTra_DTO> danhsach = new List<HoaDonTra_DTO>();
            while (reader.Read())
            {
                HoaDonTra_DTO NccDTO = new HoaDonTra_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayTra = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
        public List<HoaDonTra_DTO> LayDSTheoMaHD(string MaHD)
        {
            string sql = "SELECT * FROM PHIEUTRAHANG WHERE MaHD=" + MaHD;

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<HoaDonTra_DTO> danhsach = new List<HoaDonTra_DTO>();
            while (reader.Read())
            {
                HoaDonTra_DTO NccDTO = new HoaDonTra_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayTra = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            conn.Close();
            return danhsach;
        }
        public List<HoaDonTra_DTO> LayMaHDMoi()
        {
            string sql = "SELECT TOP 1 * FROM PHIEUTRAHANG ORDER BY MaHD DESC";

            OleDbConnection conn;
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<HoaDonTra_DTO> danhsach = new List<HoaDonTra_DTO>();
            while (reader.Read())
            {
                HoaDonTra_DTO NccDTO = new HoaDonTra_DTO();
                NccDTO.MaHD = reader.GetInt32(0);
                NccDTO.MaNCC = reader.GetInt32(1);
                NccDTO.NgayLap = reader.GetDateTime(2);
                NccDTO.NgayTra = reader.GetDateTime(3);
                NccDTO.TrangThai = reader.GetString(4);
                NccDTO.GhiChu = reader.GetString(5);
                danhsach.Add(NccDTO);
            }
            return danhsach;
        }
        public int LapHoaDon(HoaDonTra_DTO cnDTO)
        {
            string sql = "INSERT INTO PHIEUTRAHANG(MaNCC,NgayLap,NgayTra,TrangThai,GhiChu) VALUES(@MaNCC,@NgayLap,@NgayTra,@TrangThai,@GhiChu)";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@MaNCC", OleDbType.VarChar);
            para.Value = cnDTO.MaNCC;
            para = cmd.Parameters.Add("@NgayLap", OleDbType.DBDate);
            para.Value = cnDTO.NgayLap ;
            para = cmd.Parameters.Add("@NgayTra", OleDbType.DBDate);
            para.Value = cnDTO.NgayTra;
            para = cmd.Parameters.Add("@TrangThai", OleDbType.VarChar);
            para.Value = cnDTO.TrangThai;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int CapNhatHoaDonTra(HoaDonTra_DTO cnDTO)
        {
            string sql = "UPDATE PHIEUTRAHANG SET MaNCC=@MaNCC,NgayTra=@NgayTra,TrangThai=@TrangThai,GhiChu=@GhiChu WHERE MaHD=@MaHD";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;

            para = cmd.Parameters.Add("@MaNCC", OleDbType.VarChar);
            para.Value = cnDTO.MaNCC;
            para = cmd.Parameters.Add("@NgayTra", OleDbType.DBDate);
            para.Value = cnDTO.NgayTra;
            para = cmd.Parameters.Add("@TrangThai", OleDbType.VarChar);
            para.Value = cnDTO.TrangThai;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = cnDTO.GhiChu;
            para = cmd.Parameters.Add("@MaHD", OleDbType.VarChar);
            para.Value = cnDTO.MaHD;

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        public int XoaHoaDonTra(string MaHD)
        {
            string sql = "DELETE FROM PHIEUTRAHANG WHERE MaHD=" + MaHD;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if ((i >= 0))
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
    }
}
